﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace QWManage.MySqlSync
{
    internal class MsSqlDAL
    {
        public static object SqlNull(object obj)
        {
            if (obj == null)
                return DBNull.Value;

            return obj;
        }


        public static void SetParameters(MemberInfo obj, SqlCommand command)
        {
            command.Parameters.AddWithValue("@id", SqlNull(obj.id));
            command.Parameters.AddWithValue("@Owner", SqlNull(obj.Owner));
            command.Parameters.AddWithValue("@robot_id", SqlNull(obj.robot_id));
            command.Parameters.AddWithValue("@active_robot_wx_id", SqlNull(obj.active_robot_wx_id));
            command.Parameters.AddWithValue("@wx_id", SqlNull(obj.wx_id));
            command.Parameters.AddWithValue("@wx_number", SqlNull(obj.wx_number));
            command.Parameters.AddWithValue("@wx_name", SqlNull(obj.wx_name));
            command.Parameters.AddWithValue("@wx_img", SqlNull(obj.wx_img));
            command.Parameters.AddWithValue("@wx_note", SqlNull(obj.wx_note));
            command.Parameters.AddWithValue("@user_type", SqlNull(obj.user_type));
            command.Parameters.AddWithValue("@ali_pay_name", SqlNull(obj.ali_pay_name));
            command.Parameters.AddWithValue("@ali_pay_user", SqlNull(obj.ali_pay_user));
            command.Parameters.AddWithValue("@group_id", SqlNull(obj.group_id));
            command.Parameters.AddWithValue("@group_name", SqlNull(obj.group_name));
            command.Parameters.AddWithValue("@tb_pid", SqlNull(obj.tb_pid));
            command.Parameters.AddWithValue("@tb_pid_ad_zone_id", SqlNull(obj.tb_pid_ad_zone_id));
            command.Parameters.AddWithValue("@jd_pid", SqlNull(obj.jd_pid));
            command.Parameters.AddWithValue("@pdd_pid", SqlNull(obj.pdd_pid));
            command.Parameters.AddWithValue("@father_id", SqlNull(obj.father_id));
            command.Parameters.AddWithValue("@open_id", SqlNull(obj.open_id));
            command.Parameters.AddWithValue("@custom_open_id", SqlNull(obj.custom_open_id));
            command.Parameters.AddWithValue("@update_money_time", SqlNull(obj.update_money_time));
            command.Parameters.AddWithValue("@update_order_time", SqlNull(obj.update_order_time));
            command.Parameters.AddWithValue("@update_retrieve_time", SqlNull(obj.update_retrieve_time));
            command.Parameters.AddWithValue("@update_search_coupon_time", SqlNull(obj.update_search_coupon_time));
            command.Parameters.AddWithValue("@tb_order_sixbit", SqlNull(obj.tb_order_sixbit));
            command.Parameters.AddWithValue("@special_id", SqlNull(obj.special_id));
            command.Parameters.AddWithValue("@relation_id", SqlNull(obj.relation_id));
            command.Parameters.AddWithValue("@invite_poster_code", SqlNull(obj.invite_poster_code));
            command.Parameters.AddWithValue("@pay_order_fee", SqlNull(obj.pay_order_fee));
            command.Parameters.AddWithValue("@pub_order_fee", SqlNull(obj.pub_order_fee));
            command.Parameters.AddWithValue("@inv_order_fee", SqlNull(obj.inv_order_fee));
            command.Parameters.AddWithValue("@for_order_fee", SqlNull(obj.for_order_fee));
            command.Parameters.AddWithValue("@pay_order_comm", SqlNull(obj.pay_order_comm));
            command.Parameters.AddWithValue("@pub_order_comm", SqlNull(obj.pub_order_comm));
            command.Parameters.AddWithValue("@inv_order_comm", SqlNull(obj.inv_order_comm));
            command.Parameters.AddWithValue("@for_order_comm", SqlNull(obj.for_order_comm));
            command.Parameters.AddWithValue("@pay_order_num", SqlNull(obj.pay_order_num));
            command.Parameters.AddWithValue("@pub_order_num", SqlNull(obj.pub_order_num));
            command.Parameters.AddWithValue("@inv_order_num", SqlNull(obj.inv_order_num));
            command.Parameters.AddWithValue("@for_order_num", SqlNull(obj.for_order_num));
            command.Parameters.AddWithValue("@invite_fee", SqlNull(obj.invite_fee));
            command.Parameters.AddWithValue("@effec_money", SqlNull(obj.effec_money));
            command.Parameters.AddWithValue("@processing_money", SqlNull(obj.processing_money));
            command.Parameters.AddWithValue("@take_money", SqlNull(obj.take_money));
            command.Parameters.AddWithValue("@fixed_rate", SqlNull(obj.fixed_rate));
            command.Parameters.AddWithValue("@exclusive_rate", SqlNull(obj.exclusive_rate));
            command.Parameters.AddWithValue("@father_rate", SqlNull(obj.father_rate));
            command.Parameters.AddWithValue("@sign_in_fee", SqlNull(obj.sign_in_fee));
            command.Parameters.AddWithValue("@sign_in_time", SqlNull(obj.sign_in_time));
            command.Parameters.AddWithValue("@sign_in_count", SqlNull(obj.sign_in_count));
            command.Parameters.AddWithValue("@sign_in_always_count", SqlNull(obj.sign_in_always_count));
            command.Parameters.AddWithValue("@msg_star", SqlNull(obj.msg_star));
            command.Parameters.AddWithValue("@migrate", SqlNull(obj.migrate));
            command.Parameters.AddWithValue("@create_time", SqlNull(obj.create_time));
            command.Parameters.AddWithValue("@update_time", SqlNull(obj.update_time));

        }

        private static string _tableName = "eco_member";

        public static string GetClearSql()
        {
            return $"truncate table {_tableName}";
        }

        public static string GetUpdateSql()
        {
            string keyvalues = SqlHelper.GetUpdateKeyValues();
            string sql = $"Update {_tableName} set {keyvalues} where ID=@ID";
            return sql;
        }

        public static string GetInsertSql()
        {
            string columns = SqlHelper.GetInsertNameColumns();
            string values = SqlHelper.GetInsertValueColumns();
            string sql = $"INSERT INTO {_tableName} ({columns}) VALUES ({values})";
            return sql;
        }

        public static int? GetECOMemberID(string conn_str, string wxid, string owner)
        {
            using (SqlConnection conn = new SqlConnection(conn_str))
            {
                conn.Open();
                string sql = $"select id from {_tableName} where wx_id=@wxid and [owner]=@owner";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@wxid", wxid);
                cmd.Parameters.AddWithValue("@owner", owner);
                object obj = cmd.ExecuteScalar();
                if (obj != null && obj != DBNull.Value)
                {
                    return Convert.ToInt32(obj);
                }
            }
            return null;
        }

        public static Dictionary<string, List<string>> GetOwnerDic(string conn_str)
        {
            Dictionary<string, List<string>> dic = new Dictionary<string, List<string>>();
            using (SqlConnection conn = new SqlConnection(conn_str))
            {
                conn.Open();
                string sql = "select wx_img,owner from ECO_Member";
                SqlCommand cmd = new SqlCommand(sql, conn);
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string key = reader.GetString(0);
                        if (string.IsNullOrEmpty(key)) continue;
                        if (!dic.ContainsKey(key)) dic.Add(key, new List<string>());
                        string owner = reader.GetString(1);
                        if (!dic[key].Contains(owner))
                            dic[key].Add(owner);
                    }
                }
            }
            return dic;
        }

        public static void ClearAllECOStatus(string conn_str)
        {
            using (SqlConnection conn = new SqlConnection(conn_str))
            {
                conn.Open();
                string sql = "update QW_Customers set W_IsInECO=0";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
        }

        public static void UpdateECO(string conn_str, string avatar)
        {
            using (SqlConnection conn = new SqlConnection(conn_str))
            {
                conn.Open();
                string sql = "update QW_Customers set W_IsInECO=1 where Avatar=@Avatar";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Avatar", avatar);
                cmd.ExecuteNonQuery();
            }
        }
    }
}
